package com.amphiesma.gwips;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.channels.FileChannel;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.util.Log;

public class DBHandler extends SQLiteOpenHelper {

	public static final String DB_NAME = "gwipsdb.sqlite";
    private static String DB_DIR = "/data/data/com.amphiesma.gwips/databases";
    private static String DB_PATH = DB_DIR + DB_NAME;
    private static String OLD_DB_PATH = DB_DIR + "old_" + DB_NAME;	
    private static int BUFFER_SIZE = 1024;
    
    char buf[];
    
    private final Context myContext;

    private boolean createDatabase = false;    
    private boolean upgradeDatabase = false;
    
    //start column names and types
   	private static final String TABLE_SIGHTINGS = "sightings";
	
	private static final String KEY_ID = "_id";
	private static final String DATATYPE_KEY_ID = "INTEGER";
	
	
	private static final String COL_TYPE = "type";
	private static final String DATATYPE_COL_TYPE = "TEXT";
	
	private static final String COL_COMMON_NAME = "common_name";
	private static final String DATATYPE_COL_COMMON_NAME = "TEXT";
	
	private static final String COL_GENUS_NAME = "genus_name";
	private static final String DATATYPE_COL_GENUS_NAME = "TEXT";
	
	private static final String COL_SPECIES_NAME = "species_name";
	private static final String DATATYPE_COL_SPECIES_NAME = "TEXT";
	
	private static final String COL_COMMENTS = "comments";
	private static final String DATATYPE_COL_COMMENTS = "TEXT";
	
	
	private static final String COL_LATITUDE = "latitude";
	private static final String DATATYPE_COL_LATITUDE = "REAL";
	
	private static final String COL_LONGITUDE = "longitude";
	private static final String DATATYPE_COL_LONGITUDE = "REAL";
	
	private static final String COL_ALTITUDE = "altitude";
	private static final String DATATYPE_COL_ALTITUDE = "REAL";
	
	private static final String COL_ACCURACY = "accuracy";
	private static final String DATATYPE_COL_ACCURACY = "REAL";
	
	
	private static final String COL_TIME = "time";
	private static final String DATATYPE_COL_TIME = "INTEGER";
	//end column names and types
    
	public DBHandler(Context context) {
		super(context, DB_NAME, null, context.getResources().getInteger(R.string.database_version));
		myContext = context;
		DB_PATH = myContext.getDatabasePath(DB_NAME).getAbsolutePath();
		buf = new char[BUFFER_SIZE];
	}
	

	public void initializeDatabase(SQLiteDatabase db) {
		if(createDatabase) {
			
			String createStatement = "CREATE TABLE IF NOT EXISTS " + TABLE_SIGHTINGS +
					"(" + generateTableLine(KEY_ID, DATATYPE_KEY_ID, "PRIMARY KEY AUTOINCREMENT") +
					generateTableLine(COL_TYPE, DATATYPE_COL_TYPE) +
					generateTableLine(COL_COMMON_NAME, DATATYPE_COL_COMMON_NAME) +
					generateTableLine(COL_GENUS_NAME, DATATYPE_COL_GENUS_NAME) +
					generateTableLine(COL_SPECIES_NAME, DATATYPE_COL_SPECIES_NAME) +
					generateTableLine(COL_COMMENTS, DATATYPE_COL_COMMENTS) +
					generateTableLine(COL_LATITUDE, DATATYPE_COL_LATITUDE) + 
					generateTableLine(COL_LONGITUDE, DATATYPE_COL_LONGITUDE) +
					generateTableLine(COL_ALTITUDE, DATATYPE_COL_ALTITUDE) +
					generateTableLine(COL_ACCURACY, DATATYPE_COL_ACCURACY) +
					generateTableLine(COL_TIME, DATATYPE_COL_TIME, true) + 
					")";
			
			//SQLiteDatabase db = this.getWritableDatabase();
			//db.execSQL("DROP TABLE IF EXISTS " + TABLE_SIGHTINGS);
			db.execSQL(createStatement);
			Log.e("gwips:db", "apparently the create statement executed here");
//			db.close();
		}
		else if(upgradeDatabase) {
			//this is left blank for the time-being, it will probably involve 
			//uploading all the data to a web service that does the conversion into a new format
			//or alternatively doing it locally
		}
	}
		
	@Override
	public void onCreate(SQLiteDatabase db) {
		createDatabase = true;
		initializeDatabase(db);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		upgradeDatabase = true;
	}
	
	@Override
	public void onOpen(SQLiteDatabase db) {
		super.onOpen(db);
	}

	
	//Methods specific to app. Things like insertRow, updateRow, select, etc all go here 
	public void insertRow(AnimalEntry entry) {
		SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues values = new ContentValues();
		values.put(COL_TYPE, entry.getAnimalType());
		values.put(COL_COMMON_NAME, entry.getCommonName());
		values.put(COL_GENUS_NAME, entry.getGenusName());
		values.put(COL_SPECIES_NAME, entry.getSpeciesName());
		values.put(COL_COMMENTS, entry.getComments());
		values.put(COL_LATITUDE, entry.getLatitude());
		values.put(COL_LONGITUDE, entry.getLongitude());
		double alt = entry.getAltitude();
		
		if(alt != -15000)
			values.put(COL_ALTITUDE, alt);
		
		values.put(COL_ACCURACY, entry.getAccuracy());
		values.put(COL_TIME, entry.getTime());
		
		db.insert(TABLE_SIGHTINGS, null, values);
		db.close(); 
	}
	
	public void exportDB() throws FileNotFoundException, IOException {

		if(isExternalStorageWritable()) {
			File externalStorageDirectory = Environment.getExternalStorageDirectory();
			File outputFile = new File(externalStorageDirectory, DB_NAME);
			if(outputFile.exists())
				outputFile.delete();
			
			File inputFile = new File(DB_DIR + File.separator + DB_NAME);
			FileChannel src = null;
		    FileChannel dst = null;
		    try {
		        src = new FileInputStream(inputFile).getChannel();
		        dst = new FileOutputStream(outputFile).getChannel();
		        dst.transferFrom(src, 0, src.size());
		    }
		    finally {
		        if (src != null)
		            src.close();
		        if (dst != null)
		            dst.close();
		    }
			Log.e("gwips:export", outputFile.getAbsolutePath());
			
		}
		else {
			throw new IOException("Could not backup database.");
		}
			
	}	
	
	
	//utility methods
	private boolean isExternalStorageWritable() {
		String state = Environment.getExternalStorageState();
		if(Environment.MEDIA_MOUNTED.equals(state))
			return true;
		else
			return false;
	}
	private static String generateTableLine(String colName, String colType)
	{
		return generateTableLine(colName, colType, false);
	}
	
	private static String generateTableLine(String colName, String colType, boolean lastLine)
	{
		if(!lastLine)
			return colName + " " + colType + ", ";
		else
			return colName + " " + colType;
	}
	
	private static String generateTableLine(String colName, String colType, String constraints)
	{
		return generateTableLine(colName, colType, constraints, false);
	}
	
	private static String generateTableLine(String colName, String colType, String constraints, boolean lastLine)
	{
		if(!lastLine)
			return colName + " " + colType + " " + constraints + ", ";
		else
			return colName + " " + colType + " " + constraints;
	}		
	
}

